package com.dadovicn.repo.primary.auth.impl;

import com.dadovicn.auth.dsl.QSysUser;
import com.dadovicn.auth.SysUser;
import com.dadovicn.auth.SysUserQO;
import com.dadovicn.repo.primary.auth.SysUserRepo;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.QueryResults;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.jpa.JPAExpressions;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;
import java.util.List;
import java.util.Optional;

/**
 * query dsl 查询
 * @author dadovicn
 * @date   2018/9/9
 */
@Slf4j
@Component
@Transactional
public class SysUserRepoImpl {

    @Autowired
    private SysUserRepo sysUserRepo;

    @Resource
    @PersistenceContext
    private EntityManager entityManager;

    private JPAQueryFactory queryFactory;

    @PostConstruct
    public void init() {
        queryFactory = new JPAQueryFactory(entityManager);
    }

    public Optional<SysUser> findUserByUserName(final String userName, String realName){
        QSysUser quser = QSysUser.sysUser;
        Predicate predicate =  quser.mobile.eq(userName).or(quser.realName.like(realName));
        return sysUserRepo.findOne(predicate);
    }

    /**
     * jap-factory 更新
     * @param sysUserQO 页面查询对象
     * @return 执行结果
     */
    public Long updateSysUser(SysUserQO sysUserQO) {
        System.out.println(sysUserQO.getRealName());
        QSysUser qSysUser = QSysUser.sysUser;
        return queryFactory.update(qSysUser).set(qSysUser.status, true).where(qSysUser.status.isFalse()).execute();
    }

    /**
     * jpa-factory 删除
     * @param sysUserQO 页面查询对象
     * @return 执行结果
     */
    public Long deleteSysUser(SysUserQO sysUserQO) {
        QSysUser qSysUser = QSysUser.sysUser;
        System.out.println(sysUserQO.getRealName());
        return queryFactory.delete(qSysUser).where(qSysUser.status.isFalse()).execute();
    }

    /**
     * jpa-factory 查询
     * @param sysUserQO 页面查询对象
     * @return 结果集
     */
    public List<SysUser> findAllBySysUserQO(SysUserQO sysUserQO) {
        QSysUser qSysUser = QSysUser.sysUser;
        System.out.println(sysUserQO);
        queryFactory.select(qSysUser.realName).from(qSysUser).fetch();
        queryFactory.select(qSysUser.mobile).from(qSysUser).fetch();
        // 查询实体
        queryFactory.selectFrom(qSysUser).fetch();
        // 去重实体集合
        queryFactory.selectDistinct(qSysUser.mobile).from(qSysUser).fetch();
        // NonUniqueResultException fetch one 的时候需要处理这个异常, 如果存在多条的话
        // 查询条件实例
        List<SysUser> sysUserListB = queryFactory.selectFrom(qSysUser)
                //like示例
                .where(qSysUser.realName.like('%' + sysUserQO.getRealName() + '%')
                        //contain示例
                        .and(qSysUser.userName.contains("段"))
                        //equal示例
                        .and(qSysUser.status.eq(sysUserQO.isStatus()))
                        //between
                        .and(qSysUser.userId.between(20, 30)))
                .fetch();
        // 更优雅的查询方法
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qSysUser.userName.contains("段"));
        BooleanBuilder builder2 = new BooleanBuilder();
        builder2.or(qSysUser.status.isTrue());
        builder2.or(qSysUser.status.isFalse());
        builder.and(builder2);
        queryFactory.selectFrom(qSysUser).where(builder).fetch();

        // 聚合函数
        queryFactory.select(qSysUser.userId.avg()).from(qSysUser).fetchOne();
        queryFactory.select(qSysUser.userName.concat(qSysUser.realName)).from(qSysUser).fetchFirst();
        // 日期函数
        queryFactory.select(Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qSysUser.createTime)).from(qSysUser).fetchFirst();

        //子查询
        queryFactory.selectFrom(qSysUser).where(qSysUser.status.in(JPAExpressions.select(qSysUser.status).from(qSysUser))).fetch();

        //排序
        queryFactory.selectFrom(qSysUser).orderBy(qSysUser.userName.asc()).fetch();

        return sysUserListB;
    }

    /**
     * jpa-factory 分页写法
     */
    public void findWithPage() {
        QSysUser qm = QSysUser.sysUser;
        // 写法一
        JPAQuery<SysUser> query = queryFactory.selectFrom(qm).orderBy(qm.userId.asc());
        //hfetchCount的时候上面的orderBy不会被执行
        long total = query.fetchCount();
        query.offset(2).limit(5).fetch();

        // 写法二
        QueryResults<SysUser> results = queryFactory.selectFrom(qm).orderBy(qm.userId.asc()).offset(2).limit(5).fetchResults();
        results.getResults();
        log.debug("total:"+results.getTotal());
        log.debug("limit:"+results.getLimit());
        log.debug("offset:"+results.getOffset());
    }

    /**
     * jpa-factory template 写法
     */
    public void template() {
        QSysUser qm = QSysUser.sysUser;
        //使用booleanTemplate充当where子句或where子句的一部分
        queryFactory.selectFrom(qm).where(Expressions.booleanTemplate("{} = \"tofu\"", qm.userName)).fetch();
        //上面的写法，当booleanTemplate中需要用到多个占位时
        queryFactory.selectFrom(qm).where(Expressions.booleanTemplate("{0} = \"tofu\" and {1} = \"Amoy\"", qm.userId,qm.userName)).fetch();

        //使用stringTemplate充当查询语句的某一部分
        queryFactory.select(Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d')", qm.createTime)).from(qm).fetchFirst();
        // where 中的日期查询
        queryFactory.select(qm.userId).from(qm).where(Expressions.stringTemplate("DATE_FORMAT({0},'%Y-%m-%d %H:%m%s')", qm.updateTime).eq("2018-03-19")).fetchFirst();
    }

}